package org.nnxy.foodboot.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.nnxy.foodboot.entity.UserRecipeInteraction;

import java.util.List;
import java.util.Map;

/**
 * 用户食谱交互Mapper接口
 */
@Mapper
public interface UserRecipeInteractionMapper extends BaseMapper<UserRecipeInteraction> {
    
    /**
     * 获取用户最常浏览的食谱ID列表
     */
    @Select("SELECT recipe_id FROM user_recipe_interaction " +
            "WHERE user_id = #{userId} AND del_flag = '0' " +
            "ORDER BY browse_count DESC, browse_time DESC LIMIT #{limit}")
    List<Integer> getMostViewedRecipeIds(@Param("userId") Integer userId, @Param("limit") Integer limit);
    
    /**
     * 获取用户收藏的食谱ID列表
     */
    @Select("SELECT recipe_id FROM user_recipe_interaction " +
            "WHERE user_id = #{userId} AND is_favorite = 1 AND del_flag = '0' " +
            "ORDER BY favorite_time DESC LIMIT #{limit}")
    List<Integer> getFavoriteRecipeIds(@Param("userId") Integer userId, @Param("limit") Integer limit);
    
    /**
     * 获取用户高评分的食谱ID列表
     */
    @Select("SELECT recipe_id FROM user_recipe_interaction " +
            "WHERE user_id = #{userId} AND rating >= #{minRating} AND del_flag = '0' " +
            "ORDER BY rating DESC LIMIT #{limit}")
    List<Integer> getHighRatedRecipeIds(@Param("userId") Integer userId, @Param("minRating") Double minRating, @Param("limit") Integer limit);
    
    /**
     * 获取浏览量最高的食谱
     */
    @Select("SELECT r.id, r.name, r.image_url, COUNT(uri.id) as view_count " +
            "FROM recipe r " +
            "JOIN user_recipe_interaction uri ON r.id = uri.recipe_id " +
            "WHERE r.del_flag = '0' AND uri.del_flag = '0' " +
            "GROUP BY r.id " +
            "ORDER BY view_count DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> getMostViewedRecipes(@Param("limit") Integer limit);
    
    /**
     * 获取评分最高的食谱
     */
    @Select("SELECT r.id, r.name, r.image_url, AVG(uri.rating) as avg_rating, COUNT(uri.id) as rating_count " +
            "FROM recipe r " +
            "JOIN user_recipe_interaction uri ON r.id = uri.recipe_id " +
            "WHERE r.del_flag = '0' AND uri.del_flag = '0' AND uri.rating IS NOT NULL " +
            "GROUP BY r.id " +
            "HAVING rating_count >= 3 " + // 至少有3个评分
            "ORDER BY avg_rating DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> getHighestRatedRecipes(@Param("limit") Integer limit);
    
    /**
     * 获取用户最喜欢的食谱分类
     * @param userId 用户ID
     * @param limit 限制条数
     * @return 分类列表
     */
    @Select("SELECT r.category AS category, COUNT(*) AS count " +
            "FROM user_recipe_interaction uri " +
            "JOIN recipe r ON uri.recipe_id = r.id " +
            "WHERE uri.user_id = #{userId} AND (uri.is_favorite = 1 OR uri.rating >= 4) " +
            "GROUP BY r.category " +
            "ORDER BY count DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> getUserFavoriteCategories(@Param("userId") Integer userId, @Param("limit") Integer limit);
    
    /**
     * 获取用户最近的交互记录
     * @param userId 用户ID
     * @param limit 限制条数
     * @return 交互记录
     */
    @Select("SELECT uri.id, uri.recipe_id, r.name AS recipe_name, r.image_url AS recipe_image, " +
            "uri.update_time, uri.rating, uri.is_favorite, uri.browse_count, uri.make_count " +
            "FROM user_recipe_interaction uri " +
            "JOIN recipe r ON uri.recipe_id = r.id " +
            "WHERE uri.user_id = #{userId} " +
            "ORDER BY uri.update_time DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> getRecentInteractions(@Param("userId") Integer userId, @Param("limit") Integer limit);
} 